package com.mass.core.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.ObjectUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @ProjectName:  [wzlt-ht] 
 * @Package:      [com.mass.core.support.xls.utils.ExcelUtil.java]  
 * @ClassName:    [ExcelUtil]   
 * @Description:  [Excel工具类]   
 * @Author:       [张双喜]   
 * @CreateDate:   [2018年3月7日 下午11:35:58]   
 * @UpdateUser:   [zsx]   
 * @UpdateDate:   [2018年3月7日 下午11:35:58]   
 * @UpdateRemark: [说明本次修改内容] 
 * @Since         [JDK1.7]
 * @Version:      [v1.0]
 *
 */
@SuppressWarnings("deprecation")
public class ExcelUtil {
	/**
	 * 
	* @Title: getExcelAnalytical
	* @Description: 根据解析Excel表格返回
	* @param fileUrl
	* @return
	* LinkedList[] 返回类型   
	* @throws
	 */
	public static LinkedList[] getExcelAnalytical(String fileUrl){
		LinkedList[] result = new LinkedList[10];  
		FileInputStream inStream = null;
		try {
			File file = new File(fileUrl);
			inStream = new FileInputStream(file);  
		    Workbook workBook = WorkbookFactory.create(inStream);  
		    Sheet sheet = workBook.getSheetAt(0);
		    int rowNum = sheet.getLastRowNum() + 1;  
		    result = new LinkedList[rowNum];  
	        for(int i=0;i<rowNum;i++){  
	            Row row = sheet.getRow(i);  
	            //每有新的一行，创建一个新的LinkedList对象  
	            result[i] = new LinkedList();  
	            for(int j=0;j<row.getLastCellNum();j++){  
	                Cell cell = row.getCell(j);  
	                //获取单元格的值  
	                String str = getCellValue(cell);  
	                //将得到的值放入链表中  
	                result[i].add(str);  
	            }  
	        }  
	        //System.out.println(result);
			/*for(int i=0;i<result.length;i++){  
	            for(int j=0;j<result[i].size();j++){  
	                System.out.print("\t"+result[i].get(j) + "\t");  
	            }  
	            System.out.println();  
	        } */
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 
	* @Title: getExcelAnalytical
	* @Description: 上传解析Excel 
	* @param fileURL  文件绝对路径
	* @param objPackage 接收对象 package路径
	* @param number 解析起始行数
	* @param cellNames 接收对象属性名称名称
	* @return  List 返回类型   
	* @throws Exception
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static List getExcelAnalytical(String fileURL,String objPackage,Integer number,String... cellNames){
		Workbook workBook = null;
		List data = new ArrayList();
		try {
			File f = new File(fileURL);
			FileInputStream inStream = new FileInputStream(f);  
			workBook = WorkbookFactory.create(inStream);  
			if(ObjectUtils.isEmpty(number) || number < 0)
				number = 0;
		    Sheet sheet = workBook.getSheetAt(0);
		    int rowNum = sheet.getLastRowNum() + 1;  
		    //result = new LinkedList[rowNum];  
	        for(int i = number;i<rowNum;i++){  
	            Row row = sheet.getRow(i); 
	            Class c =  Class.forName(objPackage);
	            Object o = c.newInstance();
	            //每有新的一行，创建一个新的LinkedList对象  
	            //result[i] = new LinkedList();  
	            for(int j=0;j<row.getLastCellNum() && j<cellNames.length;j++){  
	                Cell cell = row.getCell(j);  
	                //获取单元格的值  
	                String str = getCellValue(cell);  
	                if(cellNames[j].contains(".")){
	                	String list[] = cellNames[j].split(".");
						String packageUrl = c.getDeclaredField(list[0]).getType().getName();
						Class cObj = Class.forName(packageUrl);
						Object obj = cObj.newInstance();
						if(cObj.getDeclaredField(list[1]).getType().getName().contains("Long")){
			                //将得到的值放入链表中  
			                Method method = cObj.getMethod("set"+toUpperCaseFirstOne(list[1]), new Class[]{Long.class});
			                method.invoke(obj, Long.getLong(str));
		            	}
		                if(cObj.getDeclaredField(list[1]).getType().getName().contains("String")){
			                //将得到的值放入链表中  
			                Method method = cObj.getMethod("set"+toUpperCaseFirstOne(list[1]), new Class[]{String.class});
			                method.invoke(obj, str);
		            	}
		                if(cObj.getDeclaredField(list[1]).getType().getName().contains("Integer")){
			                //将得到的值放入链表中  
			                Method method = cObj.getMethod("set"+toUpperCaseFirstOne(list[1]), new Class[]{Integer.class});
			                method.invoke(obj, Integer.parseInt(str));
		            	}
		                Method method = c.getMethod("set"+toUpperCaseFirstOne(list[0]), new Class[]{Object.class});
		                method.invoke(o, obj);
		                
	                }else{
	                	if(c.getDeclaredField(cellNames[j]).getType().getName().contains("Long")){
			                //将得到的值放入链表中  
			                Method method = c.getMethod("set"+toUpperCaseFirstOne(cellNames[j]), new Class[]{Long.class});
			                method.invoke(o, Long.getLong(str));
		            	}
		                if(c.getDeclaredField(cellNames[j]).getType().getName().contains("String")){
			                //将得到的值放入链表中  
			                Method method = c.getMethod("set"+toUpperCaseFirstOne(cellNames[j]), new Class[]{String.class});
			                method.invoke(o, str);
		            	}
		                if(c.getDeclaredField(cellNames[j]).getType().getName().contains("Integer")){
			                //将得到的值放入链表中  
			                Method method = c.getMethod("set"+toUpperCaseFirstOne(cellNames[j]), new Class[]{Integer.class});
			                method.invoke(o, Integer.parseInt(str));
		            	}
	                }
	                //result[i].add(str);  
	            }  
	            data.add(o);
	        }   
		} catch (Exception e) {
			e.printStackTrace();
		}
		return data;
	}
	
	/**
	 * 
	* @Title: exportExcel
	* @Description: 导出Excel
	* @param response
	* @param fileName 导出Excel 名称
	* @param data  list集合
	* @param titleNames 表格列名 value 数组
	* @param collNames 工作表名称 value 数组
	* @return void 返回类型   
	* @throws
	 */
	public static void exportExcel(
			HttpServletResponse response,
			String fileName,
			List data,
			String[] titleNames,
			String[] collNames){
		Map<String, List<Object>> data1 = new HashMap<String, List<Object>>();
		Map<String, String[]> titleNames1 = new HashMap<String, String[]>();
		Map<String, String[]> collNames1 = new HashMap<String, String[]>();
		data1.put(fileName, data);
		titleNames1.put(fileName, titleNames);
		collNames1.put(fileName, collNames);
		exportExcel(response, fileName, data1, titleNames1, collNames1);
	}
	
	public static void exportExcelExpand(HttpServletResponse response, String fileName, Map<String, List<List<Object>>> data, Map<String, String[]> titleNames) {
        ServletOutputStream out = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            Set<String> keySet = data.keySet();
            Iterator keys = keySet.iterator();
            while(keys.hasNext()) {
                String key = (String)keys.next();
                List<List<Object>> arrayList = (List<List<Object>>)data.get(key);
                HSSFSheet sheet = workbook.createSheet(key);
                String[] titles = (String[])titleNames.get(key);
                int colNum = titles.length;
                int rowNO = 0;
                createExcelRow(workbook, sheet, rowNO, -1, colNum, fileName);
                rowNO = rowNO + 1;
                createExcelRow(workbook, sheet, rowNO, 200, colNum, "制 表 日 期: " + (new SimpleDateFormat("yyyy-MM-dd")).format(new Date()), 180, "normal", "right");
                ++rowNO;
                createColumnHeader(workbook, sheet, rowNO, 300, titles);
                ++rowNO;
                String[][] strings = new String[arrayList.size()][titles.length];

                for(int i = 0; i < arrayList.size(); ++i) {
                    for(int j = 0; j<arrayList.get(i).size(); j++){
                    	Object value = arrayList.get(i).get(j);
                        if (!ObjectUtils.isEmpty(value)) {
                            strings[i][j] = value.toString();
                        } else {
                            strings[i][j] = "-";
                        }
                    }
                }
                sheet = createColumnData(workbook, sheet, rowNO, strings, arrayList.size() + 3);
                createSummaryRow(workbook, sheet, colNum, "行数合计：" + strings.length, 180, "normal", "right");
            }

            fileName = fileName + (new SimpleDateFormat("yyyyMMdd")).format(new Date());
            out = response.getOutputStream();
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1") + ".xls");
            workbook.write(out);
        } catch (Exception var35) {
            var35.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException var34) {
                var34.printStackTrace();
            }

        }
	}
	
	/**
	 * 
	* @Title: exportExcel
	* @Description: 导出Excel
	* @param response
	* @param fileName 导出Excel 名称
	* @param data 数据  key 工作表名称 value list集合
	* @param titleNames 表格列名  key 工作表名称 value 数组
	* @param collNames list集合实体名称  key 工作表名称 value 数组
	* @return void 返回类型   
	* @throws
	 */
	public static void exportExcel(
			HttpServletResponse response,
			String fileName,
			Map<String, List<Object>> data,
			Map<String, String[]> titleNames,
			Map<String, String[]> collNames){
		OutputStream out = null;  
        try { 
			HSSFWorkbook workbook = new HSSFWorkbook();
			Set<String> keySet = data.keySet();
			Iterator<String> keys = keySet.iterator();
			while(keys.hasNext()){
				String key = keys.next();
				List<Object> arrayList = data.get(key);
				HSSFSheet sheet = workbook.createSheet(key);
				String[] titles = titleNames.get(key);
				int colNum = titles.length;
				int rowNO = 0;
				//1. titleCaption
				createExcelRow(workbook, sheet, rowNO, -1, colNum, fileName); // , 250, "bold", "center"
				//2.
				rowNO++;
				createExcelRow(workbook, sheet, rowNO, 200, colNum,
				"制 表 日 期: " + new java.text.SimpleDateFormat("yyyy-MM-dd").format(new java.util.Date()), 180, "normal", "right");
				//3.columnTitleHeader
				rowNO++;
				createColumnHeader(workbook,sheet, rowNO, 300, titles);
				//4.数据行     循环创建中间的单元格的各项的值
				rowNO++;
				String colls[] = collNames.get(key); 
				String[][] strings=new String[arrayList.size()][titles.length];
				for (int i=0;i<arrayList.size();i++) {
					Object t = arrayList.get(i);
					@SuppressWarnings("unchecked")
					Class<Object> classT = (Class<Object>) arrayList.get(i).getClass();
					for(int j=0;j<titles.length;j++){
						if(colls[j].contains("[")){
							String str[] = new String[2];
							str[0] = colls[j].substring(0,colls[j].indexOf('['));
							str[1] = colls[j].substring(colls[j].indexOf('[')+1,colls[j].indexOf(']'));
							Method methodObj =  classT.getMethod("get"+toUpperCaseFirstOne(str[0]));
							Object ob = methodObj.invoke(t, new Object[0]);
							if(!ObjectUtils.isEmpty(ob)){
								Class<?> packageObj = ob.getClass();
								Method method = packageObj.getMethod("get" + toUpperCaseFirstOne(str[1]));
								Object value = method.invoke(ob, new Object[0]);
								if (!ObjectUtils.isEmpty(value))
									strings[i][j] = value.toString();
								else
									strings[i][j] = "-";
							}else {
								strings[i][j] = "-";
							}
						}else{
							Method method = classT.getMethod("get"+toUpperCaseFirstOne(colls[j]));
							Object object = method.invoke(t, new Object[0]);
							if(!ObjectUtils.isEmpty(object))
								strings[i][j]= object.toString();
							else
								strings[i][j]= "-";
						}
					}
				}
				sheet = createColumnData(workbook, sheet, rowNO,  strings, arrayList.size()+3);
				createSummaryRow(workbook, sheet, colNum, "行数合计：" + strings.length, 180, "normal", "right");
			}
        	fileName = fileName+new SimpleDateFormat("yyyyMMdd").format(new Date());
            out = response.getOutputStream();  
            response.setContentType("application/ms-excel;charset=UTF-8");  
            response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"), "ISO8859-1" )+".xls");
            workbook.write(out);  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}  
        }  	
	}
	
	//获取单元格的值  
	public static String getCellValue(Cell cell) {
	    String value = "";
	    if(!ObjectUtils.isEmpty(cell)){
		    // 以下是判断数据的类型
		    switch (cell.getCellType()) {
		    	case HSSFCell.CELL_TYPE_NUMERIC:
		    		value = cell.getNumericCellValue() + "";
		            if (HSSFDateUtil.isCellDateFormatted(cell)) {
		                Date date = cell.getDateCellValue();
		                if (date != null) {
		                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
		                } else {
		                    value = "";
		                }
		            } else {
		                value = new DecimalFormat("0.00").format(cell.getNumericCellValue());
		            }
		            break;
		        case HSSFCell.CELL_TYPE_STRING: // 字符串
		            value = cell.getStringCellValue();
		            break;
		        case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
		            value = cell.getBooleanCellValue() + "";
		            break;
		        case HSSFCell.CELL_TYPE_FORMULA: // 公式
		            value = cell.getCellFormula() + "";
		            break;
		        case HSSFCell.CELL_TYPE_BLANK: // 空值
		            value = "";
		            break;
		        case HSSFCell.CELL_TYPE_ERROR: // 故障
		            value = "";
		            break;
		        default:
		        	value = "";
		        	break;
		    }
	    }
	    return value;
	}
	
	
	/**
	 * 创建通用的Excel空白行信息
	 *
	 * @param workbook
	 *            如果为空 则没有样式
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param rowHeight
	 *            报表的单行行高
	 * @param colNum
	 *            报表的总列数 (合并)
	 */
	public void createExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowNO, int rowHeight, int colNum) {
		createExcelRow(workbook, sheet, rowNO, -1, colNum, null, -1, null, null);
	}

	/**
	 * 创建通用的Excel带标题行信息
	 *
	 * @param workbook
	 *            如果为空 则没有样式
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param rowHeight
	 *            报表的单行行高
	 * @param colNum
	 *            报表的总列数 (合并)
	 * @param fontCaption
	 *            报表行中显示的字符
	 */
	public static void createExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowNO, int rowHeight, int colNum,
			String fontCaption) {
		createExcelRow(workbook, sheet, rowNO, -1, colNum, fontCaption, -1, null, null);
	}

	/**
	 * 创建通用的Excel行信息
	 *
	 * @param workbook
	 *            如果为空 则没有样式
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param rowHeight
	 *            报表的单行行高
	 * @param colNum
	 *            报表的总列数 (合并)
	 * @param fontCaption
	 *            报表行中显示的字符
	 * @param fontSize
	 *            字体的大小 (字体大小 默认 200)
	 * @param fontWeight
	 *            报表表头显示的字符
	 * @param align
	 *            字体水平位置 (center中间 right右 left左)
	 * @param colNum
	 *            报表的列数
	 */
	public static void createExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowNO, int rowHeight, int colNum,
			String fontCaption, int fontSize, String fontWeight, String align) {
		if (colNum < 0) {
			colNum = 100;
		}

		HSSFRow row = sheet.createRow(rowNO); // 创建第一行
		row.setHeight((short) (rowHeight < 1 ? 300 : rowHeight)); // 设置行高

		HSSFCell cell = row.createCell(0);// 设置第一行
		cell.setCellType(HSSFCell.ENCODING_UTF_16); // 定义单元格为字符串类型
		cell.setCellValue(new HSSFRichTextString(fontCaption));

		sheet.addMergedRegion(new Region(rowNO, (short) 0, rowNO, (short) (colNum - 1))); // 指定合并区域

		HSSFCellStyle cellStyle = createCellFontStyle(workbook, fontSize, fontWeight, align); // 设定样式
		if (cellStyle != null) {
			cell.setCellStyle(cellStyle);
		}
	}

	/**
	 * 设置字体样式 (字体为宋体 ，上下居中对齐，可设置左右对齐，字体粗细，字体大小 )
	 *
	 * @param workbook
	 *            如果为空 则没有样式
	 * @param fontSize
	 *            字体大小 默认 200
	 * @param fontWeight
	 *            字体粗细 ( 值为bold 为加粗)
	 * @param align
	 *            字体水平位置 (center中间 right右 left左)
	 */
	public static HSSFCellStyle createCellFontStyle(HSSFWorkbook workbook, int fontSize, String fontWeight, String align) {
		if (workbook == null) {
			return null;
		}

		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		if (align != null && align.equalsIgnoreCase("left")) {
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 指定单元格居中对齐
		}
		if (align != null && align.equalsIgnoreCase("right")) {
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 指定单元格居中对齐
		}
		if (align != null && align.equalsIgnoreCase("center")) {
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		}

		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
		cellStyle.setWrapText(true);// 指定单元格自动换行

		// 单元格字体
		HSSFFont font = workbook.createFont();
		if (fontWeight != null && fontWeight.equalsIgnoreCase("normal")) {
			font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		} else {
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		}

		font.setFontName("宋体");
		font.setFontHeight((short) (fontSize < 1 ? 200 : fontSize));
		cellStyle.setFont(font);

		// 设置字体
		// HSSFFont font = workbook.createFont();
		// font.setFontHeightInPoints((short) 20); //字体高度
		// font.setColor(HSSFFont.COLOR_RED); //字体颜色
		// font.setFontName("黑体"); //字体
		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //宽度
		// font.setItalic(true); //是否使用斜体
		// font.setStrikeout(true); //是否使用划线
		// // 添加单元格注释
		// // 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器.
		// HSSFPatriarch patr = sheet.createDrawingPatriarch();
		// // 定义注释的大小和位置,详见文档
		// HSSFComment comment = patr.createComment(new HSSFClientAnchor(0, 0,
		// 0, 0, (short)4, 2, (short) 6, 5));
		// // 设置注释内容
		// comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// // 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容.
		// comment.setAuthor("Xuys.");

		return cellStyle;
	}
	/**
	 * 设置报表列头
	 * 
	 * @param workbook
	 *
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param rowHeight
	 *            报表的单行行高
	 * @param columnHeader
	 *            报表行中显示的字符
	 */
	public  static  void createColumnHeader(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[] columnHeader) {
		createColumnHeader(workbook,sheet, rowNO, rowHeight, columnHeader, -1, null, null);
	}

	/**
	 * 设置报表列头
	 *	
	 * @param workbook
	 *
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param rowHeight
	 *            报表的单行行高
	 * @param columnHeader
	 *            报表行中显示的字符
	 * @param fontSize
	 *            字体的大小 (字体大小 默认 200)
	 */
	public static void createColumnHeader(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[] columnHeader, int fontSize) {
		createColumnHeader(workbook,sheet, rowNO, rowHeight, columnHeader, fontSize, null, null);
	}

	/**
	 * 设置报表列头
	 * 
	 * @param workbook
	 *
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param rowHeight
	 *            报表的单行行高
	 * @param columnHeader
	 *            报表行中显示的字符
	 * @param fontSize
	 *            字体的大小 (字体大小 默认 200)
	 * @param fontWeight
	 *            报表表头显示的字符
	 * @param align
	 *            字体水平位置 (center中间 right右 left左)
	 */
	public static void createColumnHeader(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[] columnHeader, int fontSize,
			String fontWeight, String align) {
		if (columnHeader == null || columnHeader.length < 1) {
			return;
		}
		HSSFRow row = sheet.createRow(rowNO);
		row.setHeight((short) rowHeight);

		HSSFCellStyle cellStyle = createCellFontStyle(workbook, fontSize, fontWeight, align);
		if (cellStyle != null) {
			// 设置单元格背景色
			cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		}

		HSSFCell cell = null;
		for (int i = 0; i < columnHeader.length; i++) {
			sheet.setColumnWidth(i, 20 * 256); // 设置列宽，20个字符宽度。宽度参数为1/256，故乘以256
			cell = row.createCell(i);
			cell.setCellType(HSSFCell.ENCODING_UTF_16);
			if (cellStyle != null) {
				cell.setCellStyle(cellStyle);
			}
			cell.setCellValue(new HSSFRichTextString(columnHeader[i]));
		}
	}
	/**
	 * 创建数据行
	 * @param workbook
	 *
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param columnData
	 *            报表行中显示的数据
	 * @param maxValue
	 *            Excel显示的最大上限
	 */
	public static HSSFSheet createColumnData(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, String[][] columnData, int maxValue) {
		maxValue = (maxValue < 1 || maxValue > 65535) ? 65535 : maxValue;
		int currRowNO = rowNO;
		for (int numNO = currRowNO; numNO < columnData.length + currRowNO; numNO++) {
			if (numNO % maxValue == 0) {
				sheet = workbook.createSheet();
				rowNO = 0;
			}
			createColumnDataDesc(workbook, sheet, numNO, rowNO, currRowNO, -1, columnData);
			rowNO++;
		}
		return sheet;
	}
	

	/**
	 * 创建数据行
	 *
	 * @param sheet
	 *            (创建sheet)
	 * @param numNO
	 *            序列号
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param currRowNO
	 *            初始行号
	 * @param rowHeight
	 *            报表的单行行高
	 * @param columnData
	 *            报表行中显示的数据
	 */
	private  static  void createColumnDataDesc(HSSFWorkbook workbook,HSSFSheet sheet, int numNO, int rowNO, int currRowNO, int rowHeight,
			String[][] columnData) {
		createColumnDataDesc(workbook,sheet, numNO, rowNO, currRowNO, rowHeight, columnData, -1, null, null);
	}

	/**
	 * 创建数据行
	 * 
	 * @param workbook
	 *
	 * @param sheet
	 *            (创建sheet)
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param rowHeight
	 *            报表的单行行高
	 * @param columnData
	 *            报表行中显示的数据
	 * @param fontSize
	 *            字体大小 默认 200
	 * @param fontWeight
	 *            字体粗细 ( 值为bold 为加粗)
	 * @param align
	 *            字体水平位置 (center中间 right右 left左)
	 * @param maxValue
	 *            Excel显示的最大上限
	 */
	public static  HSSFSheet createColumnData(HSSFWorkbook workbook,HSSFSheet sheet, int rowNO, int rowHeight, String[][] columnData, int fontSize,
			String fontWeight, String align, int maxValue) {
		maxValue = (maxValue < 1 || maxValue > 65535) ? 65535 : maxValue;
		int currRowNO = rowNO;
		for (int numNO = currRowNO; numNO < columnData.length + currRowNO; numNO++) {
			if (numNO % maxValue == 0) {
				sheet = workbook.createSheet();
				rowNO = 0;
			}
			createColumnDataDesc(workbook,sheet, numNO, rowNO, currRowNO, rowHeight, columnData, fontSize, fontWeight, align);
			rowNO++;
		}
		return sheet;
	}

	/**
	 * 创建数据行
	 *
	 * @param workbook
	 *
	 * @param sheet
	 *            (创建sheet)
	 * @param numNO
	 *            序列号
	 * @param rowNO
	 *            报表的单行行号(创建第几行)
	 * @param currRowNO
	 *            初始行号
	 * @param rowHeight
	 *            报表的单行行高
	 * @param columnData
	 *            报表行中显示的数据
	 * @param fontSize
	 *            字体的大小 (字体大小 默认 200)
	 * @param fontWeight
	 *            报表表头显示的字符
	 * @param align
	 *            字体水平位置 (center中间 right右 left左)
	 */
	@SuppressWarnings("unused")
	private static void createColumnDataDesc(HSSFWorkbook workbook,HSSFSheet sheet, int numNO, int rowNO, int currRowNO, int rowHeight,
			String[][] columnData, int fontSize, String fontWeight, String align) {
		if (columnData == null || columnData.length < 1) {
			// return ;
		}
		HSSFRow row = sheet.createRow(rowNO);
		row.setHeight((short) (rowHeight < 1 ? 300 : rowHeight)); // 设置行高

		HSSFCellStyle cellStyle = null;// createCellFontStyle(workbook,
										// fontSize, fontWeight, align);
		if (cellStyle != null) {
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 指定单元格居中对齐
		} else {
			cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 指定单元格居中对齐
		}
		HSSFCell cell = null;
		for (int i = 0; i < columnData[numNO - currRowNO].length; i++) {
			sheet.setColumnWidth(i, 20 * 256); // 设置列宽，20个字符宽度。宽度参数为1/256，故乘以256
			cell = row.createCell(i);
			cell.setCellType(HSSFCell.ENCODING_UTF_16);
			if (cellStyle != null) {
				cell.setCellStyle(cellStyle);
			}
			cell.setCellValue(new HSSFRichTextString(columnData[numNO - currRowNO][i]));
		}
	}
	
	/**
	 * 创建通用的Excel最后一行的信息 (创建合计行 (最后一行))
	 *
	 * @param workbook
	 *            如果为空 则没有样式
	 * @param sheet
	 * @param colNum
	 *            报表的总列数 (合并)
	 * @param fontCaption
	 *            报表行中显示的字符
	 * @param fontSize
	 *            字体的大小 (字体大小 默认 200)
	 * @param fontWeight
	 *            报表表头显示的字符
	 * @param align
	 *            字体水平位置 (center中间 right右 left左)
	 * @param colNum
	 *            报表的列数 (需要合并到的列索引)
	 */
	public static void createSummaryRow(HSSFWorkbook workbook, HSSFSheet sheet, int colNum, String fontCaption, int fontSize,
			String fontWeight, String align) {

		HSSFCellStyle cellStyle = createCellFontStyle(workbook, fontSize, fontWeight, align);

		HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
		HSSFCell sumCell = lastRow.createCell(0);

		sumCell.setCellValue(new HSSFRichTextString(fontCaption));
		if (cellStyle != null) {
			sumCell.setCellStyle(cellStyle);
		}
		sheet.addMergedRegion(
				new Region(sheet.getLastRowNum(), (short) 0, sheet.getLastRowNum(), (short) (colNum - 1)));// 指定合并区域
	}
	
	//首字母转小写
	public static String toLowerCaseFirstOne(String s){
	  if(Character.isLowerCase(s.charAt(0)))
	    return s;
	  else
	    return (new StringBuilder()).append(Character.toLowerCase(s.charAt(0))).append(s.substring(1)).toString();
	}


	//首字母转大写
	public static String toUpperCaseFirstOne(String s){
	  if(Character.isUpperCase(s.charAt(0)))
	    return s;
	  else
	    return (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
	}
}
